package com.lcykcl.ttgen;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataRetriever {
	private static final String DB_NAME = "ttgenV2.db";
	// up to here!
	private static final String TABLE_NAME_1 = "course_info";
	private static final String TABLE_NAME_2 = "course_sched";
	private static String DB_PATH = "";
	private final Context mContext;
	protected static final String TAG = "DataRetriever";
	private SQLiteDatabase mDb;
	private DataBaseHelper mDbHelper;

	public DataRetriever(Context context) {
		this.mContext = context;
		this.mDbHelper = new DataBaseHelper(mContext);
	}

	public DataRetriever createDatabase() throws SQLException {
		try {
			mDbHelper.createDataBase();
			Log.e(TAG, "creating databse in CreateDatabase");
		} catch (IOException mIOException) {
			Log.e(TAG, "UnableToCreateDatabase in CreateDatabase");
			throw new Error("UnableToCreateDatabase");
		}
		return this;
	}

	public DataRetriever open() {
		try {
			mDbHelper.createDataBase();
			mDbHelper.openDataBase();
			mDbHelper.close();
			mDb = mDbHelper.getReadableDatabase();
			Log.e(TAG, "TRYING TO GET READABLE DATABASE");
		} catch (SQLException mSQLException) {
			Log.e(TAG, "open >>" + mSQLException.toString());
			throw mSQLException;
		} catch (IOException mIOException) {
			Log.e(TAG, "open >>" + mIOException.toString());
		}
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public ArrayList<Object> getCourseInfo(String courseCode)
			throws SQLException {
		String queryString_1 = "SELECT * FROM " + TABLE_NAME_1
				+ " WHERE code=?";
		String queryString_2 = "SELECT * FROM " + TABLE_NAME_2
				+ " WHERE code=?";
		// edited here
		String params[] = { courseCode.toUpperCase() };
		Log.e("GETCOURSEINFO", courseCode);

		try {
			Cursor cursor_1 = mDb.rawQuery(queryString_1, params);
			Cursor cursor_2 = mDb.rawQuery(queryString_2, params);
			if (cursor_1 != null) {
				Log.e("ROWCOUNT", Integer.toString(cursor_1.getCount()));
				Log.e("COLCOUNT", Integer.toString(cursor_1.getColumnCount()));
				cursor_1.moveToFirst();
				String url = cursor_1.getString(3);
				Log.e("URL", cursor_1.getString(3));
				ArrayList<Object> urlCursor = new ArrayList<Object>();
				urlCursor.add(url);
				urlCursor.add(cursor_2);
				return urlCursor;
			}
		} catch (SQLException e) {
			Log.e(TAG, "FAILED TO QUERY in getCourseInfo");
			throw e;
		} catch (Exception e) {
			Log.e(TAG, e.toString());
		}
		Log.e(TAG, "RETURNING NULL IN getCourseInfo");
		return null;

	}

	public Cursor getCourseName(String constraint) throws SQLException {
		String queryString = "SELECT * FROM " + TABLE_NAME_1;
		if (constraint != null) {
			constraint = constraint.trim() + "%";
			queryString += " WHERE code LIKE ? LIMIT 10";
		}
		String params[] = { constraint };
		Log.e(TAG, constraint + "CONTSTRAINT");

		if (constraint == null) {
			params = null;
		}
		try {
			Log.e(TAG, constraint + "TRYING TO GET THE CURSOR");
			Log.e(TAG, Long.toString(mDb.getPageSize()));
			// dbFile need to be changed!!
			// String dbFile = (mContext.getDatabasePath("courses")).toString();
			// Log.e(TAG, dbFile);
			Cursor cursor = mDb.rawQuery(queryString, params);
			Log.e(TAG, constraint + "TRYING TO QUERY");
			if (cursor != null) {
				Log.e(TAG, constraint + "FOUND SOME");
				cursor.moveToFirst();
				Log.e(TAG, constraint + "RETURN CURSOR");
				return cursor;
			}
		} catch (SQLException e) {
			Log.e(TAG, constraint + "FAILED TO QUERY");
			throw e;
		} catch (Exception e) {
			Log.e(TAG, e.toString());
		}
		Log.e(TAG, constraint + "RETURN NULL");
		return null;
	}

	public class DataBaseHelper extends SQLiteOpenHelper {
		private static final String TAG = "DataBaseHelper";
		private SQLiteDatabase mDataBase;

		public DataBaseHelper(Context context) {
			super(context, DB_NAME, null, 1);
			DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
		}

		public void createDataBase() throws IOException {
			// If database not exists copy it from the assets
			boolean mDataBaseExist = checkDataBase();

			if (!mDataBaseExist) {
				this.getReadableDatabase();
				this.close();
				try {
					// Copy the database from assests
					copyDataBase();
					Log.e(TAG, "createDatabase database created");
				} catch (IOException mIOException) {
					throw new Error("ErrorCopyingDataBase");
				}
			}
		}

		private boolean checkDataBase() {
			File dbFile = new File(DB_PATH + DB_NAME);
			// Log.v("dbFile", dbFile + "   "+ dbFile.exists());
			return dbFile.exists();
		}

		// Copy the database from assets
		private void copyDataBase() throws IOException {
			InputStream mInput = mContext.getAssets().open(DB_NAME);
			String outFileName = DB_PATH + DB_NAME;
			OutputStream mOutput = new FileOutputStream(outFileName);
			byte[] mBuffer = new byte[1024];
			int mLength;
			while ((mLength = mInput.read(mBuffer)) > 0) {
				mOutput.write(mBuffer, 0, mLength);
			}
			mOutput.flush();
			mOutput.close();
			mInput.close();
		}

		public boolean openDataBase() throws SQLException {
			String mPath = DB_PATH + DB_NAME;
			// Log.v("mPath", mPath);
			mDataBase = SQLiteDatabase.openDatabase(mPath, null,
					SQLiteDatabase.CREATE_IF_NECESSARY);
			// mDataBase = SQLiteDatabase.openDatabase(mPath, null,
			// SQLiteDatabase.NO_LOCALIZED_COLLATORS);
			return mDataBase != null;
		}

		@Override
		public synchronized void close() {
			if (mDataBase != null)
				mDataBase.close();
			super.close();
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

		}

		// Return nodes for lecture and tutorials for one semester
		// need to ask what kind of type the input will be.
		public ArrayList<ArrayList<Node>> getLTNodes(ArrayList<String> selected) {
			ArrayList<ArrayList<Node>> LTNodes = new ArrayList<ArrayList<Node>>();
			// one list for lectures
			LTNodes.add(new ArrayList<Node>());
			// another list for tutorials
			LTNodes.add(new ArrayList<Node>());
			String queryString_L = "SELECT * FROM " + TABLE_NAME_2
					+ " WHERE code=? " + "AND " + "sec_type=L";
			String queryString_T = "SELECT * FROM " + TABLE_NAME_2
					+ " WHERE code=? " + "AND " + "sec_type=T";
			int size = selected.size();

			for (int i = 0; i < size; i++) {
				String[] params = { selected.get(i) };
				Cursor cursor_1 = mDb.rawQuery(queryString_L, params);
				Cursor cursor_2 = mDb.rawQuery(queryString_T, params);
				cursor_1.moveToFirst();
				cursor_2.moveToFirst();
				ArrayList<String> Linfo = new ArrayList<String>();
				ArrayList<String> Tinfo = new ArrayList<String>();
				// My question can I get the first one??? If I move to next? :p
				while (cursor_1.moveToNext()) {
					//code
					Linfo.add(cursor_1.getString(0));
					//session
					Linfo.add(cursor_1.getString(1));
					//sec_type
					Linfo.add(cursor_1.getString(2));
					//sec_num
					Linfo.add(cursor_1.getString(3));
					//time
					Linfo.add(cursor_1.getString(4));
					//loc
					Linfo.add(cursor_1.getString(5));
					//inst
					Linfo.add(cursor_1.getString(6));
					//I don't think subtitle will be needed..

					Node LNode = new Node(Linfo);
					LTNodes.get(0).add(LNode);
				}

				while (cursor_2.moveToNext()) {

					Tinfo.add(cursor_2.getString(0));
					Tinfo.add(cursor_2.getString(1));
					Tinfo.add(cursor_2.getString(2));
					Tinfo.add(cursor_2.getString(3));
					Tinfo.add(cursor_2.getString(4));
					Tinfo.add(cursor_2.getString(5));
					Tinfo.add(cursor_2.getString(6));

					Node TNode = new Node(Tinfo);
					LTNodes.get(1).add(TNode);
				}
			}
			return LTNodes;
		}

	}
}
